package cc.autoapi.pucong.auto.flow.persistence.utils.jdbc;

import cc.autoapi.pucong.auto.flow.core.flow.execNode.exception.AutoDesignException;
import cc.autoapi.pucong.auto.flow.core.root.exception.AutoFlowException;
import cc.autoapi.pucong.auto.flow.core.root.selfenum.CommonEnum;
import cc.autoapi.pucong.auto.flow.core.root.utils.AutoFlowJdbcUtil;
import cn.hutool.core.lang.Assert;
import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.python.google.common.base.CaseFormat;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 数据库工具类
 *
 * @author zyb
 * @company www.jic.org
 * @create 2022/11/3 16:02
 */
public class DatabaseUtils {
    private static final Logger log = LoggerFactory.getLogger(DatabaseUtils.class);

    public static <T> ExeRunSqlResultPage<T> runSQLByPage(int pageNum, int pageSize, String sql, List<Object> params, Class<T> type) {
        long start = System.currentTimeMillis();
        ExeRunSqlResultPage<T> runSQLResult = new ExeRunSqlResultPage();
        try {
            // 查询总数
            Integer total = getTotal(params, sql);
            BasePageVo<T> listIPage = new BasePageVo(pageSize, pageNum);
            if (total == 0) {
                listIPage.setRecords(new ArrayList<>());
            } else {
                List<T> list = execQueryPageSql(pageNum, pageSize, params, sql, type);
                // 查询分页数据
                listIPage.setRecords(list);
            }
            // 条数
            listIPage.setTotal(total.longValue());
            // 当前页数
            listIPage.setPageNum(pageNum);
            // 总页数
            listIPage.setPages(new BigDecimal(total).divide(new BigDecimal(pageSize), RoundingMode.UP).intValue());
            // 一页大小
            listIPage.setPageSize(pageSize);
            runSQLResult.setExecResultMapPage(listIPage);
            runSQLResult.setExecMsg("ok");
            runSQLResult.setExecStatus(1);
        } catch (Exception e) {
            log.error("调试失败：" + e);
            runSQLResult.setExecMsg(e.getMessage());
            runSQLResult.setExecStatus(0);
        }
        long end = System.currentTimeMillis();
        runSQLResult.setExecTime(new BigDecimal((double) (end - start) / 1000).setScale(2, RoundingMode.HALF_UP).doubleValue());
        return runSQLResult;
    }

    private static String buildPageSql(DbType dbType, String sql, long offset, Integer pageSize) {
        if (dbType == DbType.POSTGRE_SQL) {
            return buildPgPaginationSql(sql, offset, pageSize);
        } else if (dbType == DbType.H2) {
            return buildPgPaginationSql(sql, offset, pageSize);
        } else if (dbType == DbType.MYSQL) {
            return buildMysqlPaginationSql(sql, offset, pageSize);
        } else if (dbType == DbType.SQL_SERVER) {
            return buildSqlserviceBasePaginationSql(sql, offset, pageSize);
        } else if (dbType == DbType.SQL_SERVER2005) {
            return buildSqlservice2005PaginationSql(sql, offset, pageSize);
        } else if (dbType == DbType.ORACLE) {
            return buildOraclePaginationSql(sql, offset, pageSize);
        } else if (dbType == DbType.ORACLE_12C) {
            return build12CPaginationSql(sql, offset, pageSize);
        } else {
            throw new AutoFlowException("暂不支持的类型：".concat(dbType.getDb()));
        }
    }

    private static String buildPgPaginationSql(String originalSql, long offset, long size) {
        return String.format("%s limit %s offset %s", originalSql, size, offset);
    }

    private static String buildOraclePaginationSql(String originalSql, long offset, long limit) {
        limit = offset >= 1L ? offset + limit : limit;
        return String.format("SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( %s) TMP WHERE ROWNUM <=%s) WHERE ROW_ID > %s", originalSql, limit, offset);
    }

    private static String build12CPaginationSql(String originalSql, long offset, long limit) {
        return String.format("%s OFFSET %s ROWS FETCH NEXT %s ROWS ONLY ", originalSql, offset, limit);
    }

    private static String buildMysqlPaginationSql(String originalSql, long offset, long limit) {
        return String.format("%s LIMIT %s,%s", originalSql, offset, limit);
    }

    private static String getOrderByPart(String sql) {
        String loweredString = sql.toLowerCase();
        int orderByIndex = loweredString.indexOf("order by");
        return orderByIndex != -1 ? sql.substring(orderByIndex) : "";
    }

    private static String buildSqlservice2005PaginationSql(String originalSql, long offset, long limit) {
        StringBuilder pagingBuilder = new StringBuilder();
        String orderby = getOrderByPart(originalSql);
        String distinctStr = "";
        String loweredString = originalSql.toLowerCase();
        String sqlPartString = originalSql;
        if (loweredString.trim().startsWith("select")) {
            int index = 6;
            if (loweredString.startsWith("select distinct")) {
                distinctStr = "DISTINCT ";
                index = 15;
            }

            sqlPartString = originalSql.substring(index);
        }

        pagingBuilder.append(sqlPartString);
        if (StringUtils.isBlank(orderby)) {
            orderby = "ORDER BY CURRENT_TIMESTAMP";
        }

        long firstParam = offset + 1L;
        long secondParam = offset + limit;
        String sql = "WITH selectTemp AS (SELECT " + distinctStr + "TOP 100 PERCENT  ROW_NUMBER() OVER (" + orderby + ") as __row_number__, " + pagingBuilder + ") SELECT * FROM selectTemp WHERE __row_number__ BETWEEN " + firstParam + " AND " + secondParam + " ORDER BY __row_number__";
        return sql;
    }

    public static String buildSqlserviceBasePaginationSql(String originalSql, long offset, long limit) {
        return String.format("%s OFFSET %s ROWS FETCH NEXT %s ROWS ONLY ", originalSql, offset, limit);
    }


    private static String getCountSql(String lowerCaseString) {
        String querySql = "";
        if (lowerCaseString.endsWith(";")) {
            querySql = strPlus(" select count(1) as countNum from (", lowerCaseString.substring(0, lowerCaseString.length() - 1), ") as m");
        } else {
            querySql = strPlus(" select count(1) as countNum from (", lowerCaseString, ") as m");
        }
        return querySql;
    }

    private static String strPlus(String... strs) {
        StringBuffer stringBuffer = new StringBuffer();

        for (int i = 0; i < strs.length; ++i) {
            stringBuffer.append(strs[i]);
        }
        return stringBuffer.toString();
    }

    public static Integer getTotal(List<Object> params, String sql) {
        sql = getCountSql(sql);
        JSONObject jsonObject = execQueryOneSql(params, sql, JSONObject.class);
        Integer count = jsonObject.getInteger("countnum");
        return count;
    }


    public static <T> List<T> execQueryPageSql(int pageNum, int pageSize, List<Object> params, String sql, Class<T> type) {
        Connection conn = AutoFlowJdbcUtil.getConn();
        try {
            BasePageVo page = new BasePageVo<>(pageSize, pageNum);
            DbType dbType = getDbType(conn.getMetaData().getURL());
            String pageSql = buildPageSql(dbType, sql, page.offset(), page.getPageSize());
            List<T> resultList = execQueryListSql(params, pageSql, conn, type);
            return resultList;
        } catch (Exception e) {
            log.error("执行分页查询数据异常", e);
            throw new AutoFlowException("执行sql异常", e.getMessage());
        } finally {
            AutoFlowJdbcUtil.close(conn, null, null);
        }
    }

    public static <T> T execQueryOneSql(List<Object> params, String sql, Class<T> type) {
        Connection conn = AutoFlowJdbcUtil.getConn();
        try {
            List<T> resultList = execQueryListSql(params, sql, conn, type);
            if (resultList.size() > 0) {
                return resultList.get(0);
            }
            return null;
        } catch (Exception e) {
            log.error("执行查询一条数据异常", e);
            throw new AutoFlowException("执行sql异常", e.getMessage());
        } finally {
            AutoFlowJdbcUtil.close(conn, null, null);
        }
    }

    public static <T> List<T> execQueryListSql(List<Object> params, String sql, Class<T> type) {
        Connection conn = AutoFlowJdbcUtil.getConn();
        try {
            List<T> list = execQueryListSql(params, sql, conn, type);
            return list;
        } catch (Exception e) {
            log.error("执行查询多条数据异常", e);
            throw new AutoFlowException("执行sql异常", e.getMessage());
        } finally {
            AutoFlowJdbcUtil.close(conn, null, null);
        }
    }

    public static <T> List<T> execQueryListSql(List<Object> params, String sql, Connection connection, Class<T> type) throws Exception {
        PreparedStatement pst = null;
        ResultSet rst = null;
        DbType dbType = getDbType(connection.getMetaData().getURL());
        // 执行SQL获得结果集
        try {
            log.debug("执行sql: " + sql);
            log.debug("执行参数: " + JSON.toJSONString(params));
            // 调用SQL
            pst = connection.prepareStatement(sql);
            // 参数赋值
            if (params != null) {
                for (int i = 0; i < params.size(); i++) {
                    Object value = params.get(i);
                    if (value instanceof CommonEnum) {
                        value = ((CommonEnum) value).getValue();
                    }
                    pst.setObject(i + 1, value);
                }
            }
            // 执行
            rst = pst.executeQuery();
            ResultSetMetaData rsmd = rst.getMetaData();
            // 获得结果集列数
            int columnCount = rsmd.getColumnCount();
            // 创建List
            List<ResultFiledVo> filedList = new ArrayList<>();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = rsmd.getColumnName(i);
                if (DbType.SQL_SERVER2005.equals(dbType)) {
                    if ("__row_number__".equals(columnName)) {
                        continue;
                    }
                } else if (!DbType.ORACLE_12C.equals(dbType)) {
                    if ("ROW_ID".equals(columnName)) {
                        continue;
                    }
                }
                ResultFiledVo resultFiledVo = new ResultFiledVo();
                resultFiledVo.setFiledName(columnName.toLowerCase());
                resultFiledVo.setFiledType(rsmd.getColumnTypeName(i));
                filedList.add(resultFiledVo);
            }

            List<T> list = new ArrayList<>();
            // 将ResultSet的结果保存到List中
            while (rst.next()) {
                Map<String, Object> map = new HashMap<>();
                for (int i = 0; i < filedList.size(); i++) {
                    String filedName = filedList.get(i).getFiledName();
                    Object object = rst.getObject(filedName);
                    map.put(filedName, object);
                    // 有下划线则自动转换驼峰
                    if (filedName.contains("_")) {
                        String camelName = CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, filedName);
                        map.put(camelName, object);
                    }
                }
                T t = JSON.parseObject(JSON.toJSONString(map), type);
                list.add(t);
            }
            return list;
        } catch (SQLException e) {
            log.error("执行sql异常", e);
            throw new AutoFlowException("执行sql异常" + e.getMessage());
        } finally {
            AutoFlowJdbcUtil.closeResultSet(rst);
            AutoFlowJdbcUtil.closePreparedStatement(pst);
        }
    }

    /**
     * 反射的方式获取数据，比json慢
     *
     * @param sql
     * @param params
     * @param type
     * @param <T>
     * @return
     */
    public static <T> ExeSqlResult<T> execSql(List<Object> params, String sql, Class<T> type) {
        Connection conn = AutoFlowJdbcUtil.getConn();
        try {
            ExeSqlResult<T> list = execSql(conn, sql, params, type);
            return list;
        } catch (Exception e) {
            log.error("执行查询多条数据异常", e);
            throw new AutoFlowException("执行sql异常", e.getMessage());
        } finally {
            AutoFlowJdbcUtil.close(conn, null, null);
        }
    }

    public static <T> ExeSqlResult<T> execSql(Connection con, String sql, List<Object> params, Class<T> type) {
        ExeSqlResult exeSqlResult = new ExeSqlResult();
        long start = System.currentTimeMillis();
        long end;
        PreparedStatement pst = null;
        try {
            log.debug("执行sql: " + sql);
            log.debug("执行参数: " + JSON.toJSONString(params));
            pst = con.prepareStatement(sql);
            // 参数赋值
            if (params != null) {
                for (int i = 0; i < params.size(); i++) {
                    Object value = params.get(i);
                    if (value instanceof CommonEnum) {
                        value = ((CommonEnum) value).getValue();
                    }
                    pst.setObject(i + 1, value);
                }
            }
            boolean execute = pst.execute();
            if (execute) {
                if (type == Integer.class) {
                    int updateCount = pst.getUpdateCount();
                    exeSqlResult.setResult(updateCount);
                } else if (type == Long.class) {
                    // 获取自增长id
                    ResultSet generatedKeys = pst.getGeneratedKeys();
                    while (generatedKeys.next()) {
                        long aLong = generatedKeys.getLong(1);
                        exeSqlResult.setResult(aLong);
                    }
                }
            }
            end = System.currentTimeMillis();
        } catch (Exception e) {
            log.error("[execSql Exception] --> "
                      + "the exception message is:" + e.getMessage(), e);
            end = System.currentTimeMillis();
        } finally {
            AutoFlowJdbcUtil.closePreparedStatement(pst);
        }
        exeSqlResult.setExecTime(BigDecimal.valueOf((double) (end - start) / 1000).setScale(2, RoundingMode.HALF_UP).doubleValue());
        return exeSqlResult;
    }

    public static <T> ExeSqlResult<T> getResult(ResultSet rst, Class<T> clazz) {
        ExeSqlResult<T> exeSqlResult = new ExeSqlResult();
        // 执行SQL获得结果集
        try {
            // 创建ResultSetMetaData对象
            // 结果集列数
            int columnCount = 0;
            ResultSetMetaData rsmd = rst.getMetaData();
            // 获得结果集列数
            columnCount = rsmd.getColumnCount();
            // 创建List
            List<ResultFiledVo> filedList = new ArrayList<>();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = rsmd.getColumnName(i);
                ResultFiledVo resultFiledVo = new ResultFiledVo();
                resultFiledVo.setFiledName(columnName);
                resultFiledVo.setFiledType(rsmd.getColumnTypeName(i));
                filedList.add(resultFiledVo);
            }

            List<T> list = new ArrayList<>();
            // 将ResultSet的结果保存到List中
            while (rst.next()) {
                Map<String, Object> map = new HashMap<>();
                for (int i = 0; i < filedList.size(); i++) {
                    String filedName = filedList.get(i).getFiledName();
                    Object object = rst.getObject(filedName);
                    map.put(filedName, object);
                    // 有下划线则自动转换驼峰
                    if (filedName.contains("_")) {
                        String camelName = CaseFormat.LOWER_UNDERSCORE
                                .to(CaseFormat.LOWER_CAMEL, filedName);
                        map.put(camelName, object);
                    }
                }
                //每一个map代表一条记录，把所有记录存在list中
                list.add(convert(map, clazz));
            }
            exeSqlResult.setResultList(list);
        } catch (SQLException e) {
            log.error("执行sql异常", e);
            throw new AutoFlowException("执行sql异常" + e.getMessage());
        } finally {
            AutoFlowJdbcUtil.closeResultSet(rst);
        }
        return exeSqlResult;
    }

    public static <T> T convert(Map<String, Object> map, Class<T> clazz) {
        try {
            // 创建目标类的实例
            T instance = clazz.getDeclaredConstructor().newInstance();
            // 获取目标类的所有字段
            Field[] fields = clazz.getDeclaredFields();
            for (Map.Entry<String, Object> entry : map.entrySet()) {
                String key = entry.getKey();
                Object value = entry.getValue();
                // 遍历字段，找到与Map键匹配的字段
                for (Field field : fields) {
                    if (field.getName().equals(key)) {
                        // 设置字段为可访问
                        field.setAccessible(true);

                        // 获取字段的类型
                        Class<?> fieldType = field.getType();

                        // 检查是否需要类型转换（这里简单处理，仅做示例）
                        if (fieldType.isInstance(value)) {
                            // 直接设置字段值
                            field.set(instance, value);
                        } else if (fieldType == int.class || fieldType == Integer.class) {
                            // 处理int类型
                            field.set(instance, Integer.parseInt(value.toString()));
                        } else if (fieldType == long.class || fieldType == Long.class) {
                            // 处理long类型
                            field.set(instance, Long.parseLong(value.toString()));
                        } else if (fieldType == double.class || fieldType == Double.class) {
                            // 处理double类型
                            field.set(instance, Double.parseDouble(value.toString()));
                        } else if (fieldType == boolean.class || fieldType == Boolean.class) {
                            // 处理boolean类型
                            field.set(instance, Boolean.parseBoolean(value.toString()));
                        } else if (fieldType == String.class) {
                            // 处理String类型
                            field.set(instance, value.toString());
                        } else {
                            // 其他类型可以扩展处理，或抛出异常
                            throw new IllegalArgumentException("Unsupported field type: " + fieldType.getName());
                        }
                        // 找到匹配字段后，跳出内层循环
                        break;
                    }
                }
            }
            return instance;
        } catch (NoSuchMethodException | InstantiationException | IllegalAccessException |
                 InvocationTargetException e) {
            e.printStackTrace();
            throw new AutoDesignException("Failed to convert map to object");
        }
    }

    public static DbType getDbType(String jdbcUrl) {
        Assert.isFalse(StringUtils.isBlank(jdbcUrl), "Error: The jdbcUrl is Null, Cannot read database type");
        String url = jdbcUrl.toLowerCase();
        if (url.contains(":mysql:") || url.contains(":cobar:")) {
            return DbType.MYSQL;
        } else if (url.contains(":mariadb:")) {
            return DbType.MARIADB;
        } else if (url.contains(":oracle:")) {
            return DbType.ORACLE;
        } else if (url.contains(":sqlserver:") || url.contains(":microsoft:")) {
            return DbType.SQL_SERVER2005;
        } else if (url.contains(":sqlserver2012:")) {
            return DbType.SQL_SERVER;
        } else if (url.contains(":postgresql:")) {
            return DbType.POSTGRE_SQL;
        } else if (url.contains(":hsqldb:")) {
            return DbType.HSQL;
        } else if (url.contains(":db2:")) {
            return DbType.DB2;
        } else if (url.contains(":sqlite:")) {
            return DbType.SQLITE;
        } else if (url.contains(":h2:")) {
            return DbType.H2;
        } else if (url.contains(":dm:")) {
            return DbType.DM;
        } else if (url.contains(":xugu:")) {
            return DbType.XU_GU;
        } else if (url.contains(":kingbase:") || url.contains(":kingbase8:")) {
            return DbType.KINGBASE_ES;
        } else if (url.contains(":phoenix:")) {
            return DbType.PHOENIX;
        } else if (jdbcUrl.contains(":zenith:")) {
            return DbType.GAUSS;
        } else {
            log.warn("The jdbcUrl is " + jdbcUrl + ", Mybatis Plus Cannot Read Database type or The Database's Not Supported!");
            return DbType.OTHER;
        }
    }

    public static Integer updateAutoById(String tableName, Object obj) {
        List<String> list = new ArrayList<>();
        List<Object> params = new ArrayList<>();
        Class clazz = obj.getClass();
        // 获取目标类的所有字段
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            String name = field.getName();
            if ("uuid".equals(name)) {
                continue;
            }
            if ("serialVersionUID".equals(name)) {
                continue;
            }
            // 获取值
            Object value = getFieldValueByName(name, obj);
            if (value != null) {
                if (value instanceof String) {
                    if (StringUtils.isBlank(((String) value))) {
                        continue;
                    }
                } else if (value instanceof java.util.Date) {
                    value = new Timestamp(((java.util.Date) value).getTime());
                } else if (value instanceof CommonEnum) {
                    value = ((CommonEnum) value).getValue();
                }
                // 驼峰转化为下划线
                name = humpToLine(name);
                list.add(name);
                params.add(value);
            }
        }
        // 组装修改语句
        StringBuilder sb = new StringBuilder();
        sb.append("update " + tableName + " set ");
        for (int i = 0; i < list.size(); i++) {
            sb.append(list.get(i) + " = ?");
            if (i < list.size() - 1) {
                sb.append(", ");
            }
        }
        sb.append(" where uuid = ?");
        params.add(getFieldValueByName("uuid", obj));
        ExeSqlResult<?> exeSqlResult = execSql(params, sb.toString(), obj.getClass());
        if (exeSqlResult.getResult() != null) {
            return 1;
        }
        return 0;
    }

    private static String humpToLine(String name) {
        Matcher matcher = Pattern.compile("[A-Z]").matcher(name);
        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            String matchStr = matcher.group();
            matcher.appendReplacement(sb, "_" + matchStr.toLowerCase());
        }
        matcher.appendTail(sb);
        return sb.toString();
    }

    private static Object getFieldValueByName(String name, Object obj) {
        try {
            String firstLetter = name.substring(0, 1).toUpperCase();
            String getter = "get" + firstLetter + name.substring(1);
            Method method = obj.getClass().getMethod(getter, new Class[]{});
            Object value = method.invoke(obj, new Object[]{});
            return value;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static <T> Integer saveBatchAuto(String tableName, List<T> objs) {
        if (objs.size() == 0) {
            return 0;
        }
        for (T obj : objs) {
            saveAuto(tableName, obj);
        }
        return objs.size();
    }

    public static Integer saveAuto(String tableName, Object obj) {
        List<String> list = new ArrayList<>();
        List<Object> params = new ArrayList<>();
        Class clazz = obj.getClass();
        // 获取目标类的所有字段
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            String name = field.getName();
            if ("serialVersionUID".equals(name)) {
                continue;
            }
            // 获取值
            Object value = getFieldValueByName(name, obj);
            if (value != null) {
                if (value instanceof String) {
                    if (StringUtils.isBlank(((String) value))) {
                        continue;
                    }
                } else if (value instanceof java.util.Date) {
                    value = new Timestamp(((java.util.Date) value).getTime());
                } else if (value instanceof CommonEnum) {
                    value = ((CommonEnum) value).getValue();
                }
                // 驼峰转化为下划线
                name = humpToLine(name);
                list.add(name);
                params.add(value);
            }
        }
        // 组装修改语句
        StringBuilder sb = new StringBuilder();
        sb.append("insert into " + tableName + " (");
        sb.append(String.join(",", list));
        sb.append(") values (");
        for (int i = 0; i < list.size(); i++) {
            sb.append("?");
            if (i < list.size() - 1) {
                sb.append(", ");
            }
        }
        sb.append(")");
        ExeSqlResult<?> exeSqlResult = execSql(params, sb.toString(), obj.getClass());
        if (exeSqlResult.getResult() != null) {
            return 1;
        }
        return 0;
    }
}
